﻿<%@ WebHandler Language="C#" Class="AssistMaterialsConsumeHandler" %>


/*Description: 辅材消耗定额处理程序
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer: Zhangrj 
'create Date:20240612
'Rewriter:
'Rewrite Date:
*/
using System;
using System.Web;
using BusinessRule.DTO;
using System.Web.SessionState;
using DatabaseAccess.DBUtility;
using System.Data.OracleClient;
using System.Data;
using System.Text;
using System.Linq;
using System.IO;
using Newtonsoft.Json;
using System.Collections.Generic;
using BusinessRule.Business;
using BusinessRule.IBusiness;
using BusinessRule.BusinessFactory;
using System.Collections;
using NPOI.SS.UserModel;
using Camstar.WebClient.WebServicesProxy.InSiteWebServices;

public class AssistMaterialsConsumeHandler : IHttpHandler, IRequiresSessionState
{
    CurrentUserInfo m_UserInfo;
    uLMSCommonBusiness commonBusiness = new uLMSCommonBusiness();
    string fileBasePath = System.Configuration.ConfigurationManager.AppSettings["ImportPath"].ToString();//导入临时路径

    public void ProcessRequest(HttpContext context)
    {
        try
        {
            string action = context.Request["action"].ToString();
            m_UserInfo = (CurrentUserInfo)context.Session["CurrentUserInfo"];
            if (m_UserInfo == null)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "登录过期，请重新登录" }));
                return;
            }
            switch (action)
            {
                case "GetAssistMaterialsConsumeData": //获取辅材消耗定额维护数据
                    GetAssistMaterialsConsumeData(context);
                    break;
                case "GetMaterialsConsumeInfoByID": //获取辅材消耗定额维护单条数据（通过ID）
                    GetMaterialsConsumeInfoByID(context);
                    break;
                case "SaveAssistMaterialsConsumeInfo": //保存辅材消耗定额维护数据
                    SaveAssistMaterialsConsumeInfo(context);
                    break;
                case "DeleMaterialsConsumeInfoByID": //删除辅材消耗定额维护数据（通过ID）
                    DeleMaterialsConsumeInfoByID(context);
                    break;
                case "ImportAssistMaterialsConsume": //导入辅材消耗定额维护数据
                    ImportAssistMaterialsConsume(context);
                    break;
                case "CalculateMaterialsYearDemand": //统计物料年度需求
                    CalculateMaterialsYearDemand(context);
                    break;
                case "InitMaterialsYearDemand": //初始化物料年度需求
                    InitMaterialsYearDemand(context);
                    break;
                case "ExportAssistMaterialYearDemand": //导出物料年度需求
                    ExportAssistMaterialYearDemand(context);
                    break;
                default:
                    context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = false, Message = "未知功能" }));
                    break;
            }
        }
        catch (Exception ex)
        {
            uMESProcessResult result = new uMESProcessResult();
            result.Result = false;
            result.Message = ex.Message;
            context.Response.ContentType = "text/html";
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
    }

    /// <summary>
    /// 导出辅材年度需求统计数据
    /// </summary>
    /// <param name="context"></param>
    public void ExportAssistMaterialYearDemand(HttpContext context)
    {

        DataTable dtRes = JsonConvert.DeserializeObject<DataTable>(context.Request["params"].ToString());
        foreach (DataColumn item in dtRes.Columns)
        {
            switch (item.ColumnName)
            {
                case "MATERIALNAME":
                    item.ColumnName = "物料编码"; break;
                case "MATERIALDESC":
                    item.ColumnName = "物料名称"; break;
                case "CALCULATEUNIT":
                    item.ColumnName = "计量单位"; break;
                case "LASTYEARCONSUMENUM":
                    item.ColumnName = "上年度消耗量"; break;
                case "MATERIALCATEGORY":
                    item.ColumnName = "类别"; break;
                case "DemandQty":
                    item.ColumnName = "需求数量"; break;
                case "InventoryQty":
                    item.ColumnName = "库存数量"; break;
                case "ApplyQty":
                    item.ColumnName = "申请数量"; break;
                case "StandardPrice":
                    item.ColumnName = "标准价格"; break;
                case "Money":
                    item.ColumnName = "金额"; break;
                default:
                    break;
            }
        }
        byte[] bytes = uLMS_CommonFunction.DataTableExportToExcel(dtRes);
        context.Response.Clear();
        context.Response.Buffer = true;
        context.Response.ClearHeaders();
        context.Response.ClearContent();
        context.Response.ContentType = "application/vnd.ms-excel";
        context.Response.AddHeader("fileDownload", "1");
        context.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}{1}.xls", context.Server.UrlEncode("辅材年度需求统计导出记录"), DateTime.Now.ToString("yyyyMMddHHmmssffff")));
        context.Response.AddHeader("Content-Length", bytes.Length.ToString());
        context.Response.AddHeader("Content-Type", "UTF-8");
        context.Response.Charset = "UTF-8";
        context.Response.ContentEncoding = Encoding.UTF8;
        context.Response.BinaryWrite(bytes);
        context.Response.Flush();
        context.Response.Close();
    }

    /// <summary>
    /// 初始化年度统计页面数据
    /// </summary>
    /// <param name="context"></param>
    public void InitMaterialsYearDemand(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        result.DBTable.Columns.Add("valuePen");
        result.DBTable.Columns.Add("textPen");
        for (int i = 10; i <= 300; i += 10)
        {
            DataRow dr = result.DBTable.NewRow();
            dr["valuePen"] = string.Format("{0}%", i);
            dr["textPen"] = string.Format("{0}%", i);
            result.DBTable.Rows.Add(dr);
        }
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 统计物料年度需求
    /// </summary>
    /// <param name="context"></param>
    public void CalculateMaterialsYearDemand(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        if (string.IsNullOrWhiteSpace(context.Request["mulNum"].ToString()))
        {
            result.Result = false; result.Message = "请选择系数";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        double mulNum = double.Parse(context.Request["mulNum"].ToString().TrimEnd('%')) / 100;//计算出系数
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(" WHERE ac.status = 0");
        //查询物料编码
        if (!string.IsNullOrWhiteSpace(context.Request["materialName"].ToString()))
        {
            sql.AppendFormat(" AND ac.materialName LIKE '%{0}%'", context.Request["materialName"].ToString());
        }

        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(context.Request["pageIndex"].ToString());
        pageDto.PageSize = int.Parse(context.Request["pageSize"].ToString());
        pageDto.strSQL = GetAssistMaterialsConsumeInfoSql(sql.ToString());
        result = DatabaseHelper.RunProcedureForPage(pageDto);
        double demandQty = 0;//需求数量（上年度消耗量*系数）
        double inventoryQty = 0;//库存数量（取线边库中对应数据）
        double applyQty = 0;//申请数量（需求数量-库存数量-上年度计划未到货数量(需求数量-线边库中ERP入库数量)）
        double standardPrice = 0;//标准价格（取线边库中对应数据）
        double money = 0;//金额（标准价格*申请数量）
        double lastYearConsumeNum = 0;//上年度消耗量（第一次使用取辅材基础数据中维护的上年度消耗量，后期使用需要取线边库中上年度出库量）
        result.DBTable.Columns.Add("DemandQty");
        result.DBTable.Columns.Add("InventoryQty");
        result.DBTable.Columns.Add("ApplyQty");
        result.DBTable.Columns.Add("StandardPrice");
        result.DBTable.Columns.Add("Money");
        foreach (DataRow item in result.DBTable.Rows)
        {
            lastYearConsumeNum = string.IsNullOrWhiteSpace(item["LastYearConsumeNum"].ToString()) ? 0 : double.Parse(item["LastYearConsumeNum"].ToString());
            demandQty = lastYearConsumeNum * mulNum;
            sql.Clear();
            sql.AppendFormat(@"SELECT sum(ax.qty) qty FROM auxmaterial_stock_info ax
                        WHERE ax.auxmaterialname = '{0}'
                        GROUP BY ax.auxmaterialname", item["materialname"].ToString());
            DataTable dtInventoryQty = DatabaseHelper.Query_ReturnDatTable(sql.ToString());
            inventoryQty = dtInventoryQty.Rows.Count == 0 ? 0 : int.Parse(dtInventoryQty.Rows[0]["qty"].ToString());//库存数量
            applyQty = 0;
            standardPrice = 0;
            money = 0;
            item["DemandQty"] = demandQty;//需求数量
            item["InventoryQty"] = inventoryQty;//库存数量
            item["ApplyQty"] = applyQty;//申请数量
            item["StandardPrice"] = standardPrice;//标准价格
            item["Money"] = money;//金额
        }
        result.DBTable.AcceptChanges();
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 导入主材消耗定额数据
    /// </summary>
    /// <param name="context"></param>
    public void ImportAssistMaterialsConsume(HttpContext context)
    {
        string fpath = string.Empty;
        try
        {
            var files = context.Request.Files;
            if (files.Count == 0)
            {
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "未上传文件！", Result = false }));
                return;
            }
            var file = files.Count > 1 ? files[files.Count - 1] : files[0];
            string IsXls = Path.GetExtension(file.FileName);
            string fileName = Path.GetFileNameWithoutExtension(file.FileName) + DateTime.Now.ToString("yyyyMMddHHmmssffff") + IsXls;
            fpath = context.Server.MapPath(fileBasePath + fileName);
            file.SaveAs(fpath);//保存文件，处理完成后删除

            IWorkbook Workbook = uLMS_CommonFunction.GetWorkbookObj(fpath);
            if (Workbook == null)
            {
                DeleteFile(fpath);
                context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = "解析文件出错", Result = false }));
                return;
            }
            DeleteFile(fpath);
            DataTable dtImport = uLMS_CommonFunction.ExcelImportToDataTable(Workbook);
            uMESProcessResult result = new uMESProcessResult() { Result = true, Message = "导入成功" };
            if (dtImport.Rows.Count == 0)
            {
                result.Result = false; result.Message = "解析内容出错";
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            string[] strName = { "物料编码", "物料名称", "类别", "上年度消耗量", "计量单位", "备注" };
            foreach (string str in strName)
            {
                if (!dtImport.Columns.Contains(str))
                {
                    result.Result = false; result.Message = string.Format("导入模板表头有误，缺少{0}", str);
                    break;
                }
            }
            if (!result.Result)
            {
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            result.DBTable = SaveImportMainMaterialsConsume(dtImport);
            context.Response.Write(JsonConvert.SerializeObject(result));
        }
        catch (Exception ex)
        {
            DeleteFile(fpath);//报错，则删除上传的文件
            context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Message = ex.Message, Result = false }));
        }
    }

    /// <summary>
    /// 保存导入数据
    /// </summary>
    /// <param name="dtImport"></param>
    /// <returns></returns>
    private DataTable SaveImportMainMaterialsConsume(DataTable dtImport)
    {
        StringBuilder sqlBd = new StringBuilder();
        ArrayList sqlList = new ArrayList();
        dtImport.Columns.Add("IsResult");
        dtImport.Columns.Add("ResultMessage");
        foreach (DataRow item in dtImport.Rows)
        {
            if (string.IsNullOrWhiteSpace(item["物料编码"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "物料编码为空";
                continue;
            }
            if (string.IsNullOrWhiteSpace(item["类别"].ToString()))
            {
                item["IsResult"] = false; item["ResultMessage"] = "类别为空";
                continue;
            }
            if (!string.IsNullOrWhiteSpace(item["上年度消耗量"].ToString()) && double.Parse(item["上年度消耗量"].ToString()) <= 0)
            {
                item["IsResult"] = false; item["ResultMessage"] = "上年度消耗量需大于0";
                continue;
            }
            string sql = string.Format("SELECT 1 FROM auxmaterial a WHERE a.auxmaterialname = '{0}'", item["物料编码"].ToString());
            //if (DatabaseHelper.GetSingle(sql) == null)
            //{
            //    item["IsResult"] = false; item["ResultMessage"] = "未在系统中找到物料编码";
            //    continue;
            //}
            sql = string.Format("SELECT 1 FROM AssistMaterialsConsumeInfo a WHERE a.Materialname = '{0}' AND a.Status = 0", item["物料编码"].ToString());
            if (DatabaseHelper.GetSingle(sql) != null)
            {
                item["IsResult"] = false; item["ResultMessage"] = "已存在相同的物料编码";
                continue;
            }
            sqlBd.Clear();
            sqlBd.AppendLine(@"INSERT INTO AssistMaterialsConsumeInfo(Assistmaterialsconsumeinfoid,Materialname,Materialdesc,
            Materialcategory,Lastyearconsumenum,Calculateunit,Createtime,Createby,Status,Remark) VALUES(");
            sqlBd.AppendFormat("'{0}',", Guid.NewGuid().ToString());
            sqlBd.AppendFormat("'{0}',", item["物料编码"].ToString());
            sqlBd.AppendFormat("'{0}',", item["物料名称"].ToString());
            sqlBd.AppendFormat("'{0}',", item["类别"].ToString());
            sqlBd.AppendFormat("{0},", string.IsNullOrWhiteSpace(item["上年度消耗量"].ToString()) ? "null" : item["上年度消耗量"].ToString());
            sqlBd.AppendFormat("'{0}',", item["计量单位"].ToString());
            sqlBd.AppendLine("sysdate,");
            sqlBd.AppendFormat("'{0}',", m_UserInfo.EmployeeID);
            sqlBd.AppendLine("0,");
            sqlBd.AppendFormat("'{0}')", item["备注"].ToString());
            sqlList.Add(sqlBd.ToString());
            item["IsResult"] = true; item["ResultMessage"] = "导入成功";
        }
        DatabaseHelper.ExecuteSqlTran(sqlList);
        return dtImport;
    }

    /// <summary>
    /// 获取辅材消耗定额维护数据
    /// </summary>
    /// <param name="context"></param>
    public void GetAssistMaterialsConsumeData(HttpContext context)
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine(" WHERE ac.status = 0");
        //查询物料编码
        if (!string.IsNullOrWhiteSpace(context.Request["materialName"].ToString()))
        {
            sql.AppendFormat(" AND ac.materialName LIKE '%{0}%'", context.Request["materialName"].ToString());
        }
        uMESPagingDataDTO pageDto = new uMESPagingDataDTO();
        pageDto.CurrentPageIndex = int.Parse(context.Request["pageIndex"].ToString());
        pageDto.PageSize = int.Parse(context.Request["pageSize"].ToString());
        pageDto.strSQL = GetAssistMaterialsConsumeInfoSql(sql.ToString());
        uMESProcessResult result = DatabaseHelper.RunProcedureForPage(pageDto);
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 获取主材消耗定额维护数据
    /// </summary>
    /// <param name="context"></param>
    public void GetMaterialsConsumeInfoByID(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult() { Result = true };
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(GetAssistMaterialsConsumeInfoSql(string.Format(" WHERE ac.assistmaterialsconsumeinfoid = '{0}'", context.Request["materialID"].ToString())));
        context.Response.Write(JsonConvert.SerializeObject(result));
    }
    /// <summary>
    /// 获取主材消耗定额SQL
    /// </summary>
    /// <param name="sqlWhere"></param>
    /// <returns></returns>
    public string GetAssistMaterialsConsumeInfoSql(string sqlWhere)
    {
        return @"SELECT Distinct ac.*,e.fullname createbyname,em.fullname updatebyname
                FROM AssistMaterialsConsumeInfo ac 
                LEFT JOIN auxmaterial am ON am.auxmaterialname = ac.materialname
                LEFT JOIN employee e ON e.employeeid = ac.createby
                LEFT JOIN employee em ON em.employeeid = ac.updateby " + sqlWhere;
    }

    /// <summary>
    /// 保存辅材消耗定额
    /// </summary>
    /// <param name="context"></param>
    public void SaveAssistMaterialsConsumeInfo(HttpContext context)
    {
        uMESProcessResult result = new uMESProcessResult();
        Dictionary<string, string> param = JsonConvert.DeserializeObject<Dictionary<string, string>>(context.Request["params"].ToString());
        string sql = string.Format("SELECT 1 FROM auxmaterial a WHERE a.auxmaterialname = '{0}'", param["materialName"]);
        //if (DatabaseHelper.GetSingle(sql) == null)
        //{
        //    result.Result = false; result.Message = "未在系统中找到物料编码";
        //    context.Response.Write(JsonConvert.SerializeObject(result));
        //    return;
        //}
        if (!string.IsNullOrWhiteSpace(param["lastYearConsumeNum"]) && double.Parse(param["lastYearConsumeNum"]) <= 0)
        {
            result.Result = false; result.Message = "上年度消耗量须大于0";
            context.Response.Write(JsonConvert.SerializeObject(result));
            return;
        }
        StringBuilder sqlBd = new StringBuilder();
        if (string.IsNullOrWhiteSpace(param["materialID"])) //如果ID为空，则添加
        {
            sql = string.Format("SELECT 1 FROM AssistMaterialsConsumeInfo a WHERE a.Materialname = '{0}' AND a.Status = 0", param["materialName"]);
            if (DatabaseHelper.GetSingle(sql) != null)
            {
                result.Result = false; result.Message = "已存在相同的物料编码";
                context.Response.Write(JsonConvert.SerializeObject(result));
                return;
            }
            sqlBd.AppendLine(@"INSERT INTO AssistMaterialsConsumeInfo(Assistmaterialsconsumeinfoid,Materialname,Materialdesc,
            Materialcategory,Lastyearconsumenum,Calculateunit,Createtime,Createby,Status,Remark) VALUES(");
            sqlBd.AppendFormat("'{0}',", Guid.NewGuid().ToString());
            sqlBd.AppendFormat("'{0}',", param["materialName"]);
            sqlBd.AppendFormat("'{0}',", param["materialDesc"]);
            sqlBd.AppendFormat("'{0}',", param["materialCategory"]);
            sqlBd.AppendFormat("{0},", string.IsNullOrWhiteSpace(param["lastYearConsumeNum"]) ? "null" : param["lastYearConsumeNum"]);
            sqlBd.AppendFormat("'{0}',", param["calculateUnit"]);
            sqlBd.AppendLine("sysdate,");
            sqlBd.AppendFormat("'{0}',", m_UserInfo.EmployeeID);
            sqlBd.AppendLine("0,");
            sqlBd.AppendFormat("'{0}')", param["remark"]);
        }
        else
        {
            sqlBd.AppendLine("UPDATE AssistMaterialsConsumeInfo m SET ");
            sqlBd.AppendFormat("m.MaterialName = '{0}',", param["materialName"]);
            sqlBd.AppendFormat("m.Materialdesc = '{0}',", param["materialDesc"]);
            sqlBd.AppendFormat("m.Materialcategory = '{0}',", param["materialCategory"]);
            sqlBd.AppendFormat("m.Lastyearconsumenum = {0},", double.Parse(param["lastYearConsumeNum"]));
            sqlBd.AppendFormat("m.calculateUnit = '{0}',", param["calculateUnit"]);
            sqlBd.AppendFormat("m.Remark = '{0}',", param["remark"]);
            sqlBd.AppendLine("m.updatetime = sysdate,");
            sqlBd.AppendFormat("m.updateby = '{0}' ", m_UserInfo.EmployeeID);
            sqlBd.AppendFormat("WHERE m.Assistmaterialsconsumeinfoid = '{0}'", param["materialID"]);
        }
        DatabaseHelper.ExecuteSql(sqlBd.ToString());
        result.Result = true;
        result.Message = "保存成功";
        context.Response.Write(JsonConvert.SerializeObject(result));
    }

    /// <summary>
    /// 删除辅材消耗定额数据（通过ID）
    /// </summary>
    /// <param name="context"></param>
    public void DeleMaterialsConsumeInfoByID(HttpContext context)
    {
        DataTable dtMaterial = JsonConvert.DeserializeObject<DataTable>(context.Request["datas"].ToString());
        string materialIDs = string.Empty;
        dtMaterial.AsEnumerable().ToList().ForEach(p =>
        {
            materialIDs += string.Format("'{0}',", p["materialID"].ToString());
        });
        materialIDs = materialIDs.TrimEnd(',');
        string sql = string.Format("UPDATE AssistMaterialsConsumeInfo ac SET ac.Status = 1, ac.updatetime = SYSDATE, ac.updateby = '{0}' WHERE ac.assistmaterialsconsumeinfoid IN ({1})", m_UserInfo.EmployeeID, materialIDs);
        DatabaseHelper.ExecuteSql(sql);
        context.Response.Write(JsonConvert.SerializeObject(new uMESProcessResult() { Result = true, Message = "删除成功" }));
    }

    /// <summary>
    /// 删除文件
    /// </summary>
    /// <param name="fpath">文件地址</param>
    private void DeleteFile(string fpath)
    {
        if (File.Exists(fpath))
        {
            new FileInfo(fpath).Attributes = FileAttributes.Normal;
            File.Delete(fpath);
        }
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }

}